package com.wanhua;

import java.io.IOException;
import java.sql.*;
import java.util.LinkedList;
import java.util.List;

public class AppMine {
	/**
	 * Default constructor.
	 */

	private final String driver = "com.mysql.jdbc.Driver";
	private final String url = "jdbc:mysql://whmysql-xdog2.tenxcloud.net:24360/mysql";
	private final String userid = "admin"; // 用户
	private final String passwd = "123456"; // 密码
	private Connection con;
	private Statement stmt;

	public AppMine() {
	}

	// 用于处理业务逻辑
	public String connectDB() throws IOException {
		// TODO: 在这添加你的代码
		System.out.println("service it");
		String pw = "start:";
		try {
			Class.forName(driver);
		} catch (Exception e) {
			pw = pw.concat("can not " + driver + " connect !");
			e.printStackTrace();
		}
		try {
			con = DriverManager.getConnection(url, userid, passwd);
			stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
			String sql = "select * from user";
			ResultSet rs = stmt.executeQuery(sql);
			System.out.println(rs.getRow()); // rs.getRow()获得结果集的当前行号（1、2、3等），初始为0
			while (rs.next()) {
				String aName = rs.getString("password_expired"); // 取得所在行第三列的String值
				pw = pw.concat("，" + aName);
			}
			/*
			 * sql = "update testnum set name = 'aaa' where test=2"; //sql =
			 * "select * from mysql.user"; int result = stmt.executeUpdate(sql);
			 * if(result != -1){ System.out.println("connect ok"); }
			 */
			con.close();
		} catch (SQLException SQLe) {
			pw = pw.concat(" can not link it!");
			SQLe.printStackTrace();
		}
		return pw;
	}

	public String updateDB(String args) {
		try {
			// STEP 2: Register JDBC driver
			Class.forName(driver);
			// STEP 3: Open a connection
			System.out.println("Connecting to a selected database...");
			con = DriverManager.getConnection(url, userid, passwd);
			System.out.println("Connected database successfully...");

			// STEP 4: Execute a query
			System.out.println("Creating table in given database...");
			stmt = con.createStatement();

			String sql = "CREATE TABLE REGISTRATION " + "(id INTEGER not NULL, " + " first VARCHAR(255), "
					+ " last VARCHAR(255), " + " age INTEGER, " + " PRIMARY KEY ( id ))";

			stmt.executeUpdate(sql);
			System.out.println("Created table in given database...");
		} catch (SQLException se) {
			// Handle errors for JDBC
			se.printStackTrace();
		} catch (Exception e) {
			// Handle errors for Class.forName
			e.printStackTrace();
		} finally {
			// finally block used to close resources
			try {
				if (stmt != null)
					con.close();
			} catch (SQLException se) {
			} // do nothing
			try {
				if (con != null)
					con.close();
			} catch (SQLException se) {
				se.printStackTrace();
			} // end finally try
		} // end try - by www.yiibai.com
		System.out.println("Goodbye!");
		return "";
	}// end main

	public UserList getData() {
		String pw = "";
		UserList result = new UserList();
		List<UserEntity> userList = new LinkedList<UserEntity>();
		try {
			Class.forName(driver);
		} catch (Exception e) {
			pw = pw.concat("can not connect" + driver);
			e.printStackTrace();
		}
		try {
			con = DriverManager.getConnection(url, userid, passwd);
			stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
			String sql = "select * from user";
			ResultSet rs = stmt.executeQuery(sql);
			System.out.println(rs.getRow()); // rs.getRow()获得结果集的当前行号（1、2、3等），初始为0
			while (rs.next()) {
				String us = rs.getString("user");
				String pp = rs.getString("process_priv");
				String pe = rs.getString("password_expired");
				UserEntity us1 = new UserEntity(us,pp,pe);
				userList.add(us1);
			}
			con.close();
		} catch (SQLException SQLe) {
			pw = pw.concat(" can not link it!");
			SQLe.printStackTrace();
		}
		result.setUserResult(pw);
		result.setUserList(userList);
		return result;
	}

	public void destroy() {
		System.out.println("destroy!");
	}
}